#!/usr/bin/python
# coding=utf8
from copy import copy
from traceback import print_exc

__version__ = '0.1.1'
__author__  = "Eugene Frolov"
__mail__    = "profisphantom@gmail.com"

from sqlalchemy import create_engine, Column, Integer, String, Unicode
from sqlalchemy.types import PickleType
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session, relationship, backref
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.exc import IntegrityError
from sqlalchemy.schema import ForeignKey
import pickle, time
from traceback import print_exc

from config import DATABASE_URL

Base = declarative_base()


class SerialNumberTable(Base):
    __tablename__ = "Serial_numbers"
    
    id              = Column(Integer, autoincrement=True, primary_key=True)
    serial_number   = Column(String(12), nullable=False, unique=True, index=True)
    
    def __init__(self, serial_number):
        self.serial_number = serial_number
    
    def __repr__(self):
        return "<AddressTable('%d','%s')>" % (self.id, self.serial_number)


class InventoryNumberTable(Base):
    __tablename__ = "Inventory_numbers"
    
    id              = Column(Integer, autoincrement=True, primary_key=True)
    inventory_number= Column(String(30), nullable=False, unique=True, index=True)
    
    def __init__(self, inventory_number):
        self.inventory_number = inventory_number
    
    def __repr__(self):
        return "<AddressTable('%d','%s')>" % (self.id, self.inventory_number)


class ClientTable(Base):
    __tablename__   = "Clients"
    
    id              = Column(Integer, autoincrement=True, primary_key=True)
    name            = Column(Unicode(50), nullable=False, unique=True, index=True)
    
    def __init__(self, name):
        self.name = name
    
    def __repr__(self):
        return "<ClientTable('%d','%s')>" % (self.id, self.name)


class AddressTable(Base):
    __tablename__   = "Addresses"
    
    id              = Column(Integer, autoincrement=True, primary_key=True)
    address         = Column(String(50), nullable=False, unique=True, index=True)
    
    def __init__(self, address):
        self.address = address
    
    def __repr__(self):
        return "<AddressTable('%d','%s')>" % (self.id, self.address)


class ModelTable(Base):
    __tablename__   = "Models"
    
    id              = Column(Integer, autoincrement=True, primary_key=True)
    name            = Column(String(50), nullable=False, unique=True, index=True)
    
    def __init__(self, name):
        self.name = name
    
    def __repr__(self):
        return "<ModelTable('%d','%s')>" % (self.id, self.name)


class DisignTable(Base):
    __tablename__   = "Disigns"
    
    id              = Column(Integer, autoincrement=True, primary_key=True)
    name            = Column(String(50), nullable=False, unique=True, index=True)
    
    def __init__(self, name):
        self.name = name
    
    def __repr__(self):
        return "<DisignTable('%d','%s')>" % (self.id, self.name)


class WarrantyTable(Base):
    __tablename__   = "Warrantys"
    
    id              = Column(Integer, autoincrement=True, primary_key=True)
    periud          = Column(Integer, nullable=False, unique=True, index=True)
    
    def __init__(self, periud):
        self.periud = periud
    
    def __repr__(self):
        return "<WarrantyTable('%d','%s')>" % (self.id, self.periud)


class TTNTable(Base):
    __tablename__   = "TTNs"
    
    id              = Column(Integer, autoincrement=True, primary_key=True)
    ttn             = Column(Integer, nullable=False, unique=True, index=True)
    
    def __init__(self, ttn):
        self.ttn = ttn
    
    def __repr__(self):
        return "<TTNTable('%d', '%d')>" % (self.id, self.ttn)


class TTNListTable(Base):
    __tablename__   = "TTN_lists"
    
    core_id         = Column(Integer, ForeignKey('MainRecords.id'), primary_key=True)
    ttn_id          = Column(Integer, ForeignKey('TTNs.id'), primary_key=True)
    
    ttn             = relationship(TTNTable, backref='ttn_list')
    
    def __init__(self, ttn, id=0):
        self.core_id = id
        self.ttn = ttn
    
#    def __repr__(self):
#        return "<TTNListTable('%d', '%d')>" % (self.core_id, self.ttn_id)


class SchetTable(Base):
    __tablename__   = "Schets"
    
    id              = Column(Integer, autoincrement=True, primary_key=True)
    schet           = Column(Integer, nullable=False, unique=True, index=True)
    
    def __init__(self, schet, id=0):
        self.core_id = id
        self.schet = schet
    
    def __repr__(self):
        return "<SchetTable('%d', '%d')>" % (self.id, self.schet)


class SchetListTable(Base):
    __tablename__   = "Schet_lists"
    
    core_id         = Column(Integer, ForeignKey('MainRecords.id'), primary_key=True)
    schet_id        = Column(Integer, ForeignKey('Schets.id'), primary_key=True)
    
    schet           = relationship(SchetTable, backref='schet_list')
    
    def __init__(self, schet, id=0):
        self.core_id = 0
        self.schet = schet

#    def __repr__(self):
#        return "<SchetListTable('%d', '%d')>" % (self.core_id, self.schet_id)


class CodeTable(Base):
    __tablename__   = "Codes"
    
    id              = Column(Integer, autoincrement=True, primary_key=True)
    code            = Column(String(25), nullable=False, unique=True, index=True)
    
    def __init__(self, code):
        self.code = code
    
    def __repr__(self):
        return "<CodeTable('%d', '%s')>" % (self.id, self.code)


class CommentTable(Base):
    __tablename__   = "Comments"
    
    id              = Column(Integer, autoincrement=True, primary_key=True)
    comment         = Column(String(255), nullable=False, unique=True, index=True)
    
    def __init__(self, comment):
        self.comment = comment
    
    def __repr__(self):
        return "<CommentTable('%d', '%s')>" % (self.id, self.comment)


class MainRecordTable(Base):
    __tablename__   = "MainRecords"
    
    id              = Column(Integer, autoincrement=True, primary_key=True)
    date            = Column(Integer, primary_key=True, nullable=False)
    serial_id       = Column(Integer, ForeignKey('Serial_numbers.id'), nullable=False, primary_key=True)
    inventory_id    = Column(Integer, ForeignKey('Inventory_numbers.id'), nullable=False, primary_key=True)
    client_id       = Column(Integer, ForeignKey('Clients.id'), nullable=False, primary_key=True)
    address_id      = Column(Integer, ForeignKey('Addresses.id'), nullable=False, primary_key=True)
    model_id        = Column(Integer, ForeignKey('Models.id'), nullable=False, primary_key=True)
    disign_id       = Column(Integer, ForeignKey('Disigns.id'), nullable=False, primary_key=True)
    warranty_id     = Column(Integer, ForeignKey('Warrantys.id'), nullable=False, index=True)
#    ttn_list_id     = Column(Integer, ForeignKey('TTN_list.id'), nullable=False, index=True)
#    schet_list_id   = Column(Integer, ForeignKey('Schet_list.id'), nullable=False, index=True)
    code_id         = Column(Integer, ForeignKey('Codes.id'), nullable=False, index=True)
    comment_id      = Column(Integer, ForeignKey('Comments.id'), nullable=False, index=True)
    ts_hash         = Column(Integer, nullable=False)
    
    comment1        = Column(String(50), nullable=True)
    comment2        = Column(String(50), nullable=True)
    comment3        = Column(String(50), nullable=True)
    
    serial_number   = relationship(SerialNumberTable, backref='main_row')
    inventory_number= relationship(InventoryNumberTable, backref='main_row')
    client          = relationship(ClientTable, backref='main_row')
    address         = relationship(AddressTable, backref='main_row')
    model           = relationship(ModelTable, backref='main_row')
    disign          = relationship(DisignTable, backref='main_row')
    warranty        = relationship(WarrantyTable, backref='main_row')
    ttn_list        = relationship(TTNListTable, backref='main_row')
    schet_list      = relationship(SchetListTable, backref='main_row')
#    ttn_list        = relationship(TTNTable, secondary=TTNListTable, backref='main_row_list')
#    schet_list      = relationship(SchetTable, secondary=SchetListTable, backref='main_rows_list')
    code            = relationship(CodeTable, backref='main_row')
    comment         = relationship(CommentTable, backref='main_row')
    
    def __init__(self, date, serial_number, inventory_number, client, address,
                 model, disign, warranty, ttn_list, schet_list, code, comment, 
                 ts_hash=None, comment1=None, comment2=None, comment3=None):
        self.date = date
        self.serial_number = serial_number
        self.inventory_number = inventory_number
        self.client = client
        self.address = address
        self.model = model
        self.disign = disign
        self.warranty = warranty
        self.code = code
        self.comment = comment
        self.ts_hash = int(time.time()) if not ts_hash else ts_hash
        self.ttn_list = ttn_list
        self.schet_list = schet_list
        self.comment1 = comment1
        self.comment2 = comment2
        self.comment3 = comment3
    
    def __repr__(self):
        return ("<MainRecordTable('%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d'," + \
            "'%d', '%d', '%d', '%d')>") % (self.id, self.date, self.serial_id, 
            self.inventory_id, self.client_id, self.address_id, self.model_id, 
            self.disign_id, self.warranty_id, self.code_id, self.comment_id, 
            self.ts_hash)


class SearchTable(Base):
    __tablename__   = "Search"
    
    id              = Column(Integer, autoincrement=True, primary_key=True)
    param           = Column(PickleType, nullable=False, unique=False)
    
    def __init__(self, param):
        self.param = param
    
    def __repr__(self):
        return "<ClientTable('%d','%s')>" % (self.id, self.param)


class Storage(object):
    
    table_list = {
        "Client":           ClientTable,
        "Address":          AddressTable,
        "Model":            ModelTable,
        "Warranty":         WarrantyTable,
        "Disign":           DisignTable,
        "SerialNumber":     SerialNumberTable,
        "InventoryNumber":  InventoryNumberTable,
        "Code":             CodeTable,
        "TTN":              TTNTable,
        "TTNList":          TTNListTable,
        "Schet":            SchetTable,
        "SchetList":        SchetListTable,
        "Comment":          CommentTable
    }
    
    table_search_column = {
        "Client":           'name',
        "Address":          'address',
        "Model":            'name',
        "Warranty":         'periud',
        "Disign":           'name',
        "SerialNumber":     'serial_number',
        "InventoryNumber":  'inventory_number',
        "Code":             'code',
        "TTN":              'ttn',
        "TTNList":          'ttn_id',
        "Schet":            'schet',
        "SchetList":        'schet_id',
        "Comment":          'comment'
    }
    
    def __init__(self):
        self.engine = create_engine(DATABASE_URL, echo=False)
        Base.metadata.create_all(self.engine)
        self.Session = scoped_session(sessionmaker(bind=self.engine))
    
    def delete_items(self, arr):
        session = self.Session()
        for i in arr:
            session.query(MainRecordTable)\
                   .filter(MainRecordTable.id == i)\
                   .delete()
        session.commit()
        session.close()
        return True
    
    def todict(self, obj):
        res = {}
        for i in dir(obj):
            if (i[0] != '_') and (i != "metadata") and (i != 'main_row'):
                res [i] = getattr(obj, i)
        return res
    
    def get_table(self, table, offset=0, limit=0, q=None):
        session = self.Session()
        if not q:
            result = session.query(self.table_list[table]).all()
        else:
            c = getattr(self.table_list[table], self.table_search_column[table])
            result = session.query(self.table_list[table]) \
                            .filter(c.like('%' + q + '%')) \
                            .all()
        session.close()
        return result
    
    def search_save(self, d):
        session = self.Session()
        try:
            q = session.query(SearchTable) \
                       .filter(SearchTable.param == d) \
                       .one()
        except NoResultFound:
            q = SearchTable(d)
            session.add(q)
            session.commit()
        id = q.id
        session.close()
        return id
    
    def get_search(self, id):
        session = self.Session()
        r = session.query(SearchTable).filter(SearchTable.id == id).one()
        session.close()
        return r.param
    
    def search_filter(self, params, session):
        
        try:
            params[2] = int(params[2])
        except ValueError:
            pass
        
        def r(p1, p2):
            return p1 == p2
        
        def b(p1, p2):
            return p1 > p2
        
        def m(p1, p2):
            return p1 < p2
        
        def nr(p1, p2):
            return p1 != p2
        
        def l(p1, p2):
            return p1.like(p2)
        
        operation = {
            1: r,
            2: b,
            3: m,
            4: nr,
            5: l
        }
        
        column = {
            1: operation[params[1]](MainRecordTable.date, params[2]),
            2: MainRecordTable.serial_id.in_(session.query(SerialNumberTable.id)
                .filter(operation[params[1]](SerialNumberTable.serial_number,
                params[2])).subquery()),
            3: MainRecordTable.inventory_id.in_(session.query(InventoryNumberTable.id)
                .filter(operation[params[1]](InventoryNumberTable.inventory_number,
                params[2])).subquery()),
            4: MainRecordTable.client_id.in_(session.query(ClientTable.id)
                .filter(operation[params[1]](ClientTable.name, params[2])).subquery()),
            5: MainRecordTable.address_id.in_(session.query(AddressTable.id)
                .filter(operation[params[1]](AddressTable.address, params[2])).subquery()),
            6: MainRecordTable.model_id.in_(session.query(ModelTable.id)
                .filter(operation[params[1]](ModelTable.name, params[2])).subquery()),
            7: MainRecordTable.disign_id.in_(session.query(DisignTable.id)
                .filter(operation[params[1]](DisignTable.name, params[2])).subquery()),
            8: MainRecordTable.warranty_id.in_(session.query(WarrantyTable.id)
                .filter(operation[params[1]](WarrantyTable.periud, params[2])).subquery()),
            9: MainRecordTable.id.in_(session.query(TTNListTable.core_id)
                .filter(TTNListTable.ttn_id == session.query(TTNTable.id)
                .filter(operation[params[1]](TTNTable.ttn, params[2])).subquery()).subquery()),
            10: MainRecordTable.id.in_(session.query(SchetListTable.core_id)
                .filter(SchetListTable.schet_id == session.query(SchetTable.id)
                .filter(operation[params[1]](SchetTable.schet, params[2])).subquery()).subquery()),
            11: MainRecordTable.code_id.in_(session.query(CodeTable.id)
                .filter(operation[params[1]](CodeTable.code, params[2])).subquery()),
            12: MainRecordTable.comment_id.in_(session.query(CommentTable.id)
                .filter(operation[params[1]](CommentTable.comment, params[2])).subquery()),
        }
        
        return column[params[0]]
    
    def get_main(self, qid, offset=None, limit=None):
        session = self.Session()
        try:
            search_param = self.get_search(qid)
        except NoResultFound:
            search_param = ""
        #print search_param
        r = session.query(MainRecordTable)
        if search_param != "":
            for i in search_param:
                r = r.filter(self.search_filter(i, session))
        tc = copy(r)
        if limit:
            r = r.offset(offset).limit(limit)
        result = []
        #print r
        
        for i in r.all():
            ttn_l = "<br>".join([str(x.ttn.ttn) for x in i.ttn_list])
            schet_l = "<br>".join([str(x.schet.schet) for x in i.schet_list])
            result.append({"id": i.id, "date": i.date, "client": i.client.name,
                "address": i.address.address, "model": i.model.name,
                "disign": i.disign.name, "warranty": i.warranty.periud,
                "ttn": ttn_l, "schet": schet_l, "code": i.code.code, "comment": i.comment.comment,
                "sn": i.serial_number.serial_number, "in": i.inventory_number.inventory_number});
        session.close()
        return {'main': result, 'totalCount': tc.count()}

    def check_value(self, table, value, session):
        try:
            param = getattr(self.table_list[table], self.table_search_column[table])
            obj = session.query(self.table_list[table]) \
                     .filter(param == value) \
                     .one()
        except NoResultFound:
            obj = self.table_list[table](value)
            session.add(obj)
            session.flush()
        return obj
#        try:
#            obj = self.table_list[table](value);
#            session.add(obj)
#            session.commit()
#        except IntegrityError:
#            session.rollback()
#        param = getattr(self.table_list[table], self.table_search_column[table])
#        obj = session.query(self.table_list[table]) \
#                     .filter(param == value) \
#                     .one()
#        return obj

    def save_main(self, adate, aclient, aaddress, amodel, adisign, awarranty, 
                  attn, aschet, acode, acomment, silist, comment1=None, 
                  comment2=None, comment3=None):
        try:
            session = self.Session()
            client = self.check_value('Client', aclient, session)
            address = self.check_value('Address', aaddress, session)
            model = self.check_value('Model', amodel, session)
            disign = self.check_value('Disign', adisign, session)
            warranty = self.check_value('Warranty', awarranty, session)
            code = self.check_value('Code', acode, session)
            comment = self.check_value('Comment', acomment, session)
            session_time = int(time.time())
            for si in silist:
                si['serial_number'] = '' if si['serial_number'] == None else si['serial_number']
                si['inventory_number'] = '' if si['inventory_number'] == None else si['inventory_number']
                ttn = [TTNListTable(self.check_value('TTN', int(i), session)) for i in list(set(attn))]
                schet = [SchetListTable(self.check_value('Schet', int(i), session)) for i in list(set(aschet))]
                serial_number = self.check_value('SerialNumber', si['serial_number'], session)
                inventory_number = self.check_value('InventoryNumber', si['inventory_number'], session)
                x = MainRecordTable(adate, serial_number, inventory_number, 
                    client, address, model, disign, warranty, ttn, schet, code, 
                    comment, session_time, comment1, comment2, comment3)
                session.add(x)
                session.commit()
            session.close()
            return True
#       except IntegrityError:
#           return False
        except:
            print_exc()
            raise
    
    def check_ident(self, serial_number, inventory_number):
        session = self.Session()
        try:
            if inventory_number != "":
                session.query(InventoryNumberTable) \
                       .filter(InventoryNumberTable.inventory_number == inventory_number) \
                       .one()
                return True
            else:
                session.query(SerialNumberTable) \
                       .filter(SerialNumberTable.serial_number == serial_number) \
                       .one()
                return True
        except NoResultFound:
            return False
        finally:
            session.close()
    
    def update_main(self, d):
        session = self.Session()
        id = d['id']
        q = session.query(MainRecordTable) \
                   .filter(MainRecordTable.id == id) \
                   .one()
        qlist = session.query(MainRecordTable)\
                       .filter(MainRecordTable.ts_hash == q.ts_hash)\
                       .all()
        del d['id']
        for i in d.keys():
            if i == 'date':
                value =  d[i]
                value = int(time.mktime(time.strptime(value, "%Y-%m-%dT%H:%M:%S")))
                q.date = value
                for j in qlist:
                    j.date = value
            elif i == 'sn':
                sn = self.check_value('SerialNumber', d[i], session)
                q.serial_number = sn
            elif i == 'in':
                inventory_number = self.check_value('InventoryNumber', d[i], session)
                q.inventory_number = inventory_number
            elif i == 'client':
                client = self.check_value('Client', d[i], session)
                q.client = client
                for j in qlist:
                    j.client = client
            elif i == 'address':
                address = self.check_value('Address', d[i], session)
                q.address = address
                for j in qlist:
                    j.address = address
            elif i == 'model':
                model = self.check_value('Model', d[i], session)
                q.model = model
                for j in qlist:
                    j.model = model
            elif i == 'disign':
                disign = self.check_value('Disign', d[i], session)
                q.disign = disign
                for j in qlist:
                    j.disign = disign
            elif i == 'warranty':
                warranty = self.check_value('Warranty', d[i], session)
                q.warranty = warranty
                for j in qlist:
                    j.warranty = warranty
            elif i == 'code':
                code = self.check_value('Code', d[i], session)
                q.code = code
                for j in qlist:
                    j.code = code
            elif i == 'ttn':
                attn = d[i].replace('<br>', '/')
                #session.query(TTNListTable).filter(TTNListTable.core_id == id).delete()
                #ttn = [TTNListTable(self.check_value('TTN', int(k), session), id) for k in attn.split('/')]
                #q.ttn_list = ttn
                for j in qlist:
                    session.query(TTNListTable).filter(TTNListTable.core_id == j.id).delete()
                    ttn = [TTNListTable(self.check_value('TTN', int(k), session), j.id) for k in attn.split('/')]
                    j.ttn_list = ttn
            elif i == 'schet':
                aschet = d[i].replace('<br>', '/')
                #session.query(SchetListTable).filter(SchetListTable.core_id == id).delete()
                #schet = [SchetListTable(self.check_value('Schet', int(k), session), id) for k in aschet.split('/')]
                #q.schet_list = schet
                for j in qlist:
                    session.query(SchetListTable).filter(SchetListTable.core_id == j.id).delete()
                    schet = [SchetListTable(self.check_value('Schet', int(k), session), j.id) for k in aschet.split('/')]
                    j.schet_list = schet
        session.commit()
        session.close()
                

    def xls_export(self, qid):
        import pyExcelerator as pEx
        import datetime, tempfile, os
        r = self.get_main(qid)
        w = pEx.Workbook()
        ws = w.add_sheet('данные')
        i = 0
        style = pEx.XFStyle()
        style.num_format_str = "DD.MM.YYYY"
        style_str = pEx.XFStyle()
        style_str.num_format_str = '@'
        ws.write(i, 0, u"#", style_str)
        ws.write(i, 1, u"Дата", style_str)
        ws.write(i, 2, u"Серийный номер", style_str)
        ws.write(i, 3, u"Инвентарный номер", style_str)
        ws.write(i, 4, u"Клиент", style_str)
        ws.write(i, 5, u"Адрес", style_str)
        ws.write(i, 6, u"Модель", style_str)
        ws.write(i, 7, u"Дизайн", style_str)
        ws.write(i, 8, u"Гарантия", style_str)
        ws.write(i, 9, u"№ ТТН", style_str)
        ws.write(i, 10, u"№ Счета", style_str)
        ws.write(i, 11, u"Код", style_str)
        ws.write(i, 12, u"Коментарий", style_str)
        i += 1
        for row in r['main']:
            ws.write(i, 0, i)
            ws.write(i, 1, datetime.datetime.fromtimestamp(row['date']), style)
            ws.write(i, 2, row['sn'], style_str)
            ws.write(i, 3, row['in'], style_str)
            ws.write(i, 4, row['client'], style_str)
            ws.write(i, 5, row['address'], style_str)
            ws.write(i, 6, row['model'], style_str)
            ws.write(i, 7, row['disign'], style_str)
            ws.write(i, 8, row['warranty'], style_str)
            ws.write(i, 9, row['ttn'].replace('<br>', '/'), style_str)
            ws.write(i, 10, row['schet'].replace('<br>', '/'), style_str)
            ws.write(i, 11, row['code'], style_str)
            ws.write(i, 12, row['comment'], style_str)
            i += 1
        unicname = os.path.basename(tempfile.mkstemp(prefix='report_')[1])
        unicname += '.xls'
        w.save('/var/www/frigoglass/tmp/' + unicname)
        return "/frigoglass/tmp/" + unicname
